/******************************************************************************
 * @File name   :      IMemberPackageRedemptionDao.java
 *
 * @Author      :      JIZHANGS
 *
 * @Date        :      Jul 31, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2015 Capgemini, Inc. All  Rights Reserved.
 * This software is published under the terms of the Capgemini Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * 
 * ----------------------------------------------------------------------------
 * Date                   Who         Version        Comments
 * Jul 31, 2015 11:16:42 AM        JIZHANGS     1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.redemption.dao.impl;

import java.util.List;

import javax.inject.Inject;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.member.constants.MaintainMemberConstants;
import com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao;
import com.cap.bts.crmdp.dealer.redemption.domain.MemberPackageRedemption;
import com.cap.bts.crmdp.dealer.redemption.dto.RedemptionSubmitDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;

@Repository("packageRedemptionDao")
public class PackageRedemptionDaoImpl extends BaseDaoImpl<MemberPackageRedemption, Long> implements IPackageRedemptionDao {
   
        /**
         * @Date        :      Jul 31, 2015
         * @param clazz
         */
        @Inject
        public PackageRedemptionDaoImpl(@Value("com.cap.bts.crmdp.dealer.redemption.domain.MemberPackageRedemption") Class<MemberPackageRedemption> clazz) {
            super(clazz);
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      Aug 2, 2015
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#getPackageListDetailByPackageId(java.lang.Long)
        **/
        @Override
        public List<Object[]> getPackageListDetailByPackageId(Long packageId,Integer status) throws AppException {
            String sql = "select a.PACKAGE_ID, a.LINE_ID, a.SHORT_NAME, b.DESCRIPTION, count(1),"
                + " IF(c.VERIFICATION_METHOD='CODE','Y','N'),a.REDEEM_DATE,MIN(a.ID) as id,c.DEDUCTION_AMOUNT,c.DEDUCTION_METHOD"
                + " from TM_MEMBER_PACKAGE_REDEMPTION a "
                + " inner join tm_raw_package_redemption_lines b on a.LINE_ID = b.ID"
                + " inner join tm_raw_ticket_def  c on c.id=b.TICKET_DEF_ID"
                + " where a.PACKAGE_ID="+packageId +" "
                    + " and a.STATUS="+status
                    //+ " and a.MARS_LOCK_STATUS='0'"
                    +" group by a.PACKAGE_ID, a.LINE_ID, b.SHORT_NAME, b.DESCRIPTION"
                    + " order by a.id asc";
            return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback(sql));
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      Aug 3, 2015
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#checkActivicationCode(com.cap.bts.crmdp.dealer.redemption.dto.RedemptionSubmitDto)
        **/
        @Override
        public MemberPackageRedemption checkActivicationCode(RedemptionSubmitDto dto) throws AppException {
            List<MemberPackageRedemption> list=super.getListByQuery("from MemberPackageRedemption a"
                + " where a.id="+dto.getDetailId()
                + " and a.packageId="+dto.getId()
                //激活码失效
                + " and a.activicationCode='"+dto.getActivicationCode()+"'");
            MemberPackageRedemption mpr=null;
            if(list!=null && !list.isEmpty()){
               mpr=list.get(0);
            }else{
                List<MemberPackageRedemption> otherList=super.getListByQuery("from MemberPackageRedemption a"
                    + " where a.packageRedemptionLine.id="+dto.getLineId()
                    + " and a.status !="+MaintainMemberConstants.PACKAGE_HAS_USED
                    + " and a.packageId="+dto.getId());
                if(otherList!=null && !otherList.isEmpty()){
                    for(int i=0;i<otherList.size();i++){
                        if(otherList.get(i).getActivicationCode().equals(dto.getActivicationCode())){
                            mpr = otherList.get(i);
                            dto.setDetailId(otherList.get(i).getId());
                        }
                    }
                }
            }
            return mpr;
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      Aug 4, 2015
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#updateMemberPackageRedemption(com.cap.bts.crmdp.dealer.redemption.domain.MemberPackageRedemption)
        **/
        @Override
        public void updateMemberPackageRedemption(MemberPackageRedemption mp) throws AppException {
            super.save(mp);
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      2015年9月9日
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#useActivicationCode(com.cap.bts.crmdp.dealer.redemption.dto.RedemptionSubmitDto)
        **/
        @Override
        public MemberPackageRedemption useActivicationCode(RedemptionSubmitDto dto)
            throws AppException {
            List<MemberPackageRedemption> list=super.getListByQuery("from MemberPackageRedemption a where a.id="+dto.getDetailId()+"and a.packageId="+dto.getId());
            MemberPackageRedemption mpr=null;
            if(list!=null && !list.isEmpty()){
               mpr=list.get(0);
            }
            return mpr;
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      2015年9月18日
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#getPackageListReportDetailByPackageId(java.lang.Long, java.lang.Integer)
        **/
        @Override
        public List<Object[]> getPackageListReportDetailByPackageId(Long packageId)
            throws AppException {
            String sql = "select a.PACKAGE_ID, a.LINE_ID, b.SHORT_NAME, b.DESCRIPTION,"
                + " SUM("
                + "case when a.STATUS=0 then 1 else 0 end"
                + " ) unused_qty,"
                + "SUM("
                + "case when a.STATUS=1 then 1 else 0 end"
                + ") used_qty"
                + " from TM_MEMBER_PACKAGE_REDEMPTION a "
                + " inner join tm_raw_package_redemption_lines b on a.LINE_ID = b.ID"
                + " where a.PACKAGE_ID="+packageId
                    +" group by a.PACKAGE_ID, a.LINE_ID, b.SHORT_NAME, b.DESCRIPTION";
            return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback(sql));
        }
        
        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      2015年10月29日
         * @author annson
        **/
        @Override
        public List<Object[]> getPackageListReportDetailByPackageIds(List<Long> packageIds)
            throws AppException {
        	
        	StringBuilder idBuilder = new StringBuilder();
        	for (int i = 0; i < packageIds.size(); i++) {
        		if(i > 0){
        			idBuilder.append(",");
        		}
        		idBuilder.append(packageIds.get(i));
			}
        	
        	String sql = "select  " +
                     "        a.PACKAGE_ID,  " +
                     "        a.LINE_ID,  " +
                     "        b.SHORT_NAME,  " +
                     "        b.DESCRIPTION,  " +
                     "        SUM(case   " +
                     "            when a.STATUS=0 then 1   " +
                     "            else 0   " +
                     "        end ) unused_qty,  " +
                     "        SUM(case   " +
                     "            when a.STATUS=1 then 1   " +
                     "            else 0   " +
                     "        end) used_qty   " +
                     "    from  " +
                     "      (select c.* from  TM_MEMBER_PACKAGE_REDEMPTION c where  " +
                     "       c.PACKAGE_ID in (" + idBuilder.toString() + ")) a    " +
                     "    inner join  " +
                     "        tm_raw_package_redemption_lines b   " +
                     "            on a.LINE_ID = b.ID   " +
                     "      " +
                     "    group by  " +
                     "        a.PACKAGE_ID,  " +
                     "        a.LINE_ID,  " +
                     "        b.SHORT_NAME,  " +
                     "        b.DESCRIPTION ";
            return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback(sql));
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      2015年9月22日
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#getPackageRedemListByPackageId(java.lang.Long)
        **/
        @Override
        public List<Object[]> getPackageRedemListByPackageId(Long packageId) throws AppException {
            String sql = "select a.PACKAGE_NUMBER, b.SHORT_NAME, b.REDEEM_DATE,b.SO_NUMBER_REDEEM,b.SALES_ORDER_LINE"
                + " from tm_member_package_info a,tm_member_package_redemption b"
                + " where b.PACKAGE_ID="+packageId
                    +" and a.ID=b.PACKAGE_ID"
                    + " and b.REDEEM_DATE is not null"
                    + " AND b.status = 1"
                    + " and b.MARS_LOCK_STATUS != 1"
                    + " order by b.REDEEM_DATE desc";
            return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback(sql));
        }

        /**
         * {@inheritDoc} 
         * overridden:
         * @Date        :      2015年9月22日
         * @see com.cap.bts.crmdp.dealer.redemption.dao.IPackageRedemptionDao#getPackageMemberBenfitByPackageId(java.lang.Long)
        **/
        @Override
        public List<String> getPackageMemberBenfitByPackageId(Long packageId) throws AppException {
            String sql = "select a.MEMBER_BENFIT"
                + " from tm_crm_package_definition a,tm_member_package_info b"
                + " where b.ID="+packageId
                    +" and b.PACKAGE_DEF_ID=a.ID";
            return getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback(sql));
        }
        
        @Override
        public void deleteByPackageId(Long packageId) throws AppException {
        	String sql1 = "delete FROM tm_raw_package_redemption_lines_item  where PACKAGE_ID="+packageId;
        	String sql2 = "delete from tm_member_package_redemption where PACKAGE_ID="+packageId;
        	super.getEntityManager().createNativeQuery(sql1).executeUpdate();
        	super.getEntityManager().createNativeQuery(sql2).executeUpdate();
        }
        
        @Override
        public Long getUsedRedemptionCountByPackageNumber(String packageNumber) throws AppException {
        	long count = 0L;
        	String sql = "SELECT " +
    				" count(*) " +
    				" FROM " +
    				" tm_member_package_redemption " +
    				" WHERE " +
    				" STATUS = 1 " +
    				" AND EXISTS ( " +
    				" SELECT " +
    				"  1 " +
    				" FROM " +
    				"   tm_member_package_info " +
    				" WHERE " +
    				"   PACKAGE_ID = id " +
    				" AND " +
    				"   PACKAGE_NUMBER = '"+ packageNumber + "' " +
    				")";
        	Query countQuery = super.getEntityManager().createNativeQuery(sql);
        	Object singleRsult=countQuery.getSingleResult();
            if(singleRsult!=null){
            	count = Long.parseLong(singleRsult.toString());
            }
            return count;
        }
}
